//
//  SNFmdbManager.m
//  app
//
//  Created by 王少宁 on 15/4/29.
//  Copyright (c) 2015年 hdaren. All rights reserved.
//

#import "SNFmdbManager.h"
#import "NSDictionary+SafetyGet.h"

@interface SNFmdbManager ()

@end

@implementation SNFmdbManager

+ (SNFmdbManager *)shareManager
{
    AS_SINGLETON(SNFmdbManager, manager, {
        manager = [[SNFmdbManager alloc] init];
        manager.fmdbQueue = nil;
    })
    return manager;
}

- (void)loadDatabaseQueue{
    //_fmdbQueue = [FMDatabaseQueue databaseQueueWithPath:kDBPath];
//    if (_fmdbQueue == nil) {
//        _fmdbQueue = [FMDatabaseQueue databaseQueueWithPath:kDBPath];
//    }
    if (_fmdbQueue == nil) {
        HDAppDelegate *appDelegate = (HDAppDelegate *)[UIApplication sharedApplication].delegate;
        _fmdbQueue = appDelegate.fmdbQueue;
    }
}

- (FMDatabaseQueue *)fmdbQueue{
    if (_fmdbQueue == nil) {
        HDAppDelegate *appDelegate = (HDAppDelegate *)[UIApplication sharedApplication].delegate;
        _fmdbQueue = appDelegate.fmdbQueue;
    }
    return _fmdbQueue;
}

#pragma mark - 是否应该自动刷新
- (BOOL)shouldRefreshSelfWithLastRequestTime:(NSDate *)lastRequestDate refreshSelfTimeInterval:(float)timeInterval tableName:(NSString *)tableName
{
    NSFileManager * fm = [NSFileManager defaultManager];
    
    if ([fm fileExistsAtPath:kDBPath]) {//数据库文件已经存在
        //判断表是不是存在:
        if ([self fmdbIncludetableWithTableName:tableName]) {//表是存在的
            //通过时间判断是否需要自动刷新
            NSTimeInterval aTimer = [[NSDate date] timeIntervalSinceDate:lastRequestDate];
            DLog(@"时间差  ===== %f",aTimer);
            if (aTimer > timeInterval) {//已经有一段时间没有刷新了,需要自动刷新
                DLog(@"需要自动刷新");
                return YES;
            } else {//不需要自动刷新
                DLog(@"不需要自动刷新");
                return NO;
            }
        } else {//表不存在,第一次进入此页面
            return YES;
        }
    } else {//数据库文件都不存在,第一次进入这个页面
        return YES;
    }
}

#pragma mark - 创建表
- (BOOL)createTableWithSql:(NSString *)sql
{
    __block BOOL success = NO;
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        NSError *error = nil;
        BOOL flag = [db executeUpdate:sql withErrorAndBindings:&error];
        if (!flag) {
            success = NO;
            DLog(@"执行创建表语句: '%@'失败. \n失败原因: %@", sql, [error localizedDescription]);
        } else {
            DLog(@"执行创建表语句: '%@'成功",sql);
            success = YES;
        }

    }];
    return success;
}

- (BOOL)createTableWithTableName:(NSString *)tableName
{
    __block BOOL successCreateTable = NO;
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        NSString * sqlStr = [NSString stringWithFormat:@"create table if not exists %@(ID integer primary key autoincrement, Data blob);",tableName];
        
        BOOL flag = [db executeUpdate:sqlStr];
        if (flag) {
            DLog(@"创建表 %@ 成功",tableName);
            successCreateTable = YES;
        } else {
            DLog(@"创建表 %@ 失败",tableName);
            successCreateTable = NO;
        }

    }];
    
    
    return successCreateTable;
}

#pragma mark - 表是否存在
- (BOOL)fmdbIncludetableWithTableName:(NSString *)tableName
{
    NSString *querySQL = [NSString stringWithFormat:@"select count(*) as table_count from sqlite_master t where t.type = 'table' and t.name = '%@'", tableName];
    
    [self loadDatabaseQueue];
    __block BOOL isExist = NO;
    NSFileManager * fm = [NSFileManager defaultManager];
    if ([fm fileExistsAtPath:kDBPath]) {//数据库文件已经存在
      
        [_fmdbQueue inDatabase:^(FMDatabase *db) {
            
            FMResultSet *rs = [db executeQuery:querySQL];
            if([rs next]){
                isExist = [rs intForColumn:@"table_count"];
            }
            [rs close];
            
        }];

    } else {//数据库文件都不存在,第一次进入这个页面
        isExist = NO;
    }
    
    return isExist;
}

#pragma mark - 表中是否存有数据
- (BOOL)tabelIncludeDataWithTableName:(NSString *)tableName
{
    __block BOOL includeData = NO;
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {
        
        NSString * sql_one = [NSString stringWithFormat:@"select * from sqlite_master where name  = '%@';",tableName];
        
        FMResultSet * set = [db executeQuery:sql_one];
        
        BOOL setFlag = [set next];
        if (setFlag){
            NSString * sql_two = [NSString stringWithFormat:@"select * from %@;",tableName];
            FMResultSet * rs = [db executeQuery:sql_two];
            
            BOOL rsFlag = [rs next];
            if (rsFlag) {
                DLog(@"表格中数据是存在的");
                includeData = YES;
            } else {
                DLog(@"表格中数据不存在");
                includeData =  NO;
            }
            [rs close];
            
        } else {
            includeData =  NO;
        }
        [set close];
    }];
    
    return includeData;
}

#pragma mark - 删除表
-(BOOL)deletetableWithTableName:(NSString *)tableName
{
    __block BOOL dropTable = NO;
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        FMResultSet * set = [db executeQuery:[NSString stringWithFormat:@"select * from sqlite_master where name  = '%@';",tableName]];
        BOOL flag = [set next];
        [set close];
        if (flag) {//表是存在的
            BOOL dropFlag = [db executeUpdate:[NSString stringWithFormat:@"drop table %@;",tableName]];
            if (dropFlag) {
                DLog(@"表格存在,并且已经删除");
                dropTable = YES;
            } else {
                DLog(@"表格存在,但未删除");
                dropTable = NO;
            }
        } else {
            DLog(@"表格不存在,不用执行删除语句");
            dropTable = NO;
        }
        
    }];
    

    return dropTable;
}

#pragma mark - 将第一页的数据保存到数据库
- (BOOL)saveDataToTableWithData:(NSData*)data tableName:(NSString *)tableName
{
    __block BOOL saveDataToTable = NO;
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        NSString * setStr = [NSString stringWithFormat:@"insert into %@ (Data) values (?);",tableName];
        BOOL flag = [db executeUpdate: setStr,data];
        if (flag) {
            DLog(@"已经将数据添加到了 %@ 表中",tableName);
            saveDataToTable = YES;
        } else {
            DLog(@"没有将数据添加到 %@ 表中",tableName);
            saveDataToTable = NO;
        }

    }];
    

    return saveDataToTable;
}

#pragma mark - 从数据库中取出第一页的数据
- (NSData *)dataFromTableWithTableName:(NSString *)tableName
{
    __block NSData * dataFromTable;
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        NSString * setStr = [NSString stringWithFormat:@"select * from sqlite_master where name = '%@';",tableName];
        FMResultSet * set = [db executeQuery:setStr];
        BOOL setFlag = [set next];
        
        if (setFlag){
            NSString * rsStr = [NSString stringWithFormat:@"select * from %@;",tableName];
            FMResultSet * rs = [db executeQuery:rsStr];
            BOOL rsFlag = [rs next];
            if (rsFlag) {
                dataFromTable = [rs dataForColumn:@"Data"];
            }
            [rs close];
        }
        [set close];
    }];
    
    return dataFromTable;
}

-(NSMutableArray*)dataFromTable:(NSString*)tableName
{
    NSMutableArray * dicArr = [NSMutableArray array];
    [self loadDatabaseQueue];
    
    [_fmdbQueue inDatabase:^(FMDatabase *db) {
        NSString * sql_one = [NSString stringWithFormat:@"select * from sqlite_master where name   = '%@';",tableName];
        FMResultSet * set_one = [db executeQuery:sql_one];
        if ([set_one next]){
            NSString * sql_two = [NSString stringWithFormat:@"select * from %@;",tableName];
            FMResultSet * set = [db executeQuery:sql_two];
            
            if ([set next]) {
                NSString * ownerAddId = [set stringForColumn:@"ownerAddId"];
                NSString * lastTalk = [set stringForColumn:@"content"];
                NSString * time = [set stringForColumn:@"time"];
                NSString * uid = [set stringForColumn:@"uid"];
                NSString * fuid = [set stringForColumn:@"fuid"];
                NSString * msgid = [set stringForColumn:@"msgid"];
                NSString * lastid = [set stringForColumn:@"lastid"];
                NSString * picture = [set stringForColumn:@"picture"];
                NSString * type = [set stringForColumn:@"type"];
                NSString * mytype = [set stringForColumn:@"mtype"];
                NSString * extends = [set stringForColumn:@"extends"];
                DLog(@"extends = %@",extends);
                NSData *JSONData = [extends dataUsingEncoding:NSUTF8StringEncoding];
                NSDictionary *extendsDic = [NSJSONSerialization JSONObjectWithData:JSONData options:NSJSONReadingMutableLeaves error:nil];
                NSString * flower = [set stringForColumn:@"flower"];
                NSString * red = [set stringForColumn:@"red"];
                
                NSDictionary * dict = [NSDictionary dictionaryWithObjectsAndKeys:ownerAddId,@"ownerAddId",fuid,@"fuid",time,@"dateline",lastTalk,@"content",uid,@"uid",msgid,@"msgid",lastid,@"lastid",picture,@"picture",type,@"type",mytype,@"mtype",extendsDic,@"extends",flower,@"flower",red,@"red", nil];
                
                
                [dicArr addObject:dict];
            }
            [set close];
        }
        [set_one close];
//        [db close];
    }];
//    [_fmdbQueue close];
    
    return dicArr;
}
- (NSMutableArray *)arrayFromFmdbWithArray:(NSMutableArray *)maArray
{
    NSMutableArray * arr1 = [NSMutableArray array];
    
    
    [self.fmdbQueue inDatabase:^(FMDatabase *db) {
        NSString * s = [NSString stringWithFormat:@"select * from sqlite_master where name  = 'ChatList_%@';",[[NSUserDefaults standardUserDefaults] objectForKey:@"uid"]];
        FMResultSet * set_one = [db executeQuery:s] ;
        if ([set_one next]){
            NSString * sql = [NSString stringWithFormat:@"select * from ChatList_%@;",[[NSUserDefaults standardUserDefaults] objectForKey:@"uid"]] ;
            FMResultSet * set = [db executeQuery:sql];
            [maArray removeAllObjects];
            
            while ([set next]) {
                
                NSString * pmid = [set stringForColumn:@"pmid"];
                NSString * name = [set stringForColumn:@"name"];
                NSString * face = [set stringForColumn:@"face"];
                NSString * uid = [set stringForColumn:@"uid"];
                NSString * lastTalk = [[NSString alloc]init];
                NSString * reply = [NSString stringWithFormat:@"reply%@",uid];
                if ([pmid isEqualToString:reply]) {
                    lastTalk = [NSString stringWithFormat:@"%@回复了您的帖子",name];
                }else
                {
                    lastTalk = [set stringForColumn:@"lastTalk"];
                }
                NSString * time = [set stringForColumn:@"time"];
                NSString * msgid = [set stringForColumn:@"msgid"];
                NSString * delSign = [set stringForColumn:@"delSign"];
                
                NSDictionary * dict = @{@"pmid":pmid,
                                        @"face":face,
                                        @"nickname":name,
                                        @"dateline":time,
                                        @"content":lastTalk,
                                        @"uid":uid,
                                        @"msgid":msgid,
                                        @"delSign":delSign};
                
                //                [arr1 addObject:[HDChatListModel modelWithDict:dict]];
                [arr1 addObject:dict];
            }
            [set close];
        }
        [set_one close];
        //        [db close];
    }];
    return arr1;
}


#pragma mark - 执行 sql 的 executeUpdate 语句
- (BOOL)performExecuteUpdateWithSql:(NSString *)sql
{
    __block BOOL executeUpdate = NO;
    [self loadDatabaseQueue];
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        BOOL flag = [db executeUpdate:sql];
        if (flag) {
            executeUpdate = YES;
        } else {
            executeUpdate = NO;
        }
        
    }];
    
    return executeUpdate;
}

- (BOOL)performExecuteUpdateWithSql:(NSString *)sql withArguments:(NSArray *)arguments{
    [self loadDatabaseQueue];
    __block BOOL isSuccess = NO;
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        isSuccess = [db executeUpdate:sql withArgumentsInArray:arguments];

    }];
    
    return isSuccess;
}

- (SNResultSet *)performExecuteQueryWithSql:(NSString *)sql
{
    [self loadDatabaseQueue];
    __block SNResultSet * resultSet = nil;
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        FMResultSet * rs = [db executeQuery:sql];
        resultSet = [self convertResultSet:rs];
        
        [rs close];

    }];
    
    return resultSet;
}

- (SNResultSet *)performExecuteQueryWithSql:(NSString *)sql withArguments:(NSArray *)arguments{
    [self loadDatabaseQueue];
    __block SNResultSet *resultSet = nil;
    [_fmdbQueue inDatabase:^(FMDatabase *db) {

        FMResultSet *rs = [db executeQuery:sql withArgumentsInArray:arguments];
        resultSet = [self convertResultSet:rs];
        
        [rs close];

    }];
    

    return resultSet;
}

#pragma -mark 结果集转换

- (SNResultSet *)convertResultSet:(FMResultSet *)resultSet{
    NSMutableArray *resultSetArray = [NSMutableArray array];
    NSInteger columnCount = [resultSet columnCount];
    
    while ([resultSet next]) {
        NSMutableDictionary *valuesMap = [NSMutableDictionary dictionary];
        for(int index = 0; index < columnCount; index ++){
            // 以小写key存储
            NSString *columnName = [[resultSet columnNameForIndex:index] lowercaseString];
            [valuesMap setValue:[resultSet objectForColumnIndex:index] forKey:columnName];
        }
        [resultSetArray addObject:[valuesMap copy]];
    }
    
    return [[SNResultSet alloc] initWithDataArray:[resultSetArray copy] columnNameToIndexMap:resultSet.columnNameToIndexMap];
}

- (NSArray *)arrayWithVaList:(va_list)vaList{
    NSMutableArray *mutableArray = [NSMutableArray array];
    id va = nil;
    while((va = va_arg(vaList,id)))  {
        if(va != nil){
            [mutableArray addObject:va];
        }
    }
    
    return [mutableArray copy];
}

//- (void)dealloc{
//    [_fmdbQueue close];
//}

@end


@interface SNResultSet(){
    NSUInteger _rowCount;
    NSUInteger _columnCount;
}

@property (nonatomic, assign) NSInteger currentRow;
@property (nonatomic, strong) NSMutableDictionary *columnIndexToNameMap;
@property (nonatomic, strong) NSDictionary *columnNameToIndexMap;
@property (nonatomic, strong) NSArray *dataArray;

@end

@implementation SNResultSet

static NSString *columnIndexToNameKey = @"ColumnIndexToNameKey";

- (instancetype)initWithDataArray:(NSArray *)dataArray columnNameToIndexMap:(NSDictionary *)columnNameToIndexMap{
    if(self = [super init]){
        _columnCount = 0;
        _rowCount = [dataArray count];
        _currentRow = -1;
        self.columnNameToIndexMap = columnNameToIndexMap;
        self.dataArray = dataArray;
    }
    return self;
}

- (NSMutableDictionary *)columnIndexToNameMap{
    if(_columnIndexToNameMap == nil){
        _columnIndexToNameMap = [NSMutableDictionary dictionary];
    }
    return _columnIndexToNameMap;
}

- (void)setColumnNameToIndexMap:(NSMutableDictionary *)columnNameToIndexMap{
    if(_columnNameToIndexMap == nil){
        _columnCount = [columnNameToIndexMap count];
        _columnNameToIndexMap = columnNameToIndexMap;
        NSArray *values = [columnNameToIndexMap allValues];
        NSArray *keys = [columnNameToIndexMap allKeys];
        for(NSInteger index = 0; index < keys.count; index ++){
            NSString *key = [NSString stringWithFormat:@"%@%@", columnIndexToNameKey, values[index]];
            [self.columnIndexToNameMap setValue:keys[index] forKey:key];
        }
    }
}

- (BOOL)hasNext{
    
    NSInteger currentRow = [[NSNumber numberWithInteger:self.currentRow] integerValue];
    NSInteger rowCount = [[NSNumber numberWithInteger:self.rowCount] integerValue];
    BOOL hasNextRow = (rowCount > 0 && currentRow < rowCount - 1);
    if(hasNextRow){
        self.currentRow ++;
    }
    return hasNextRow;
}

- (NSUInteger)rowCount{
    return _rowCount;
}

- (NSUInteger)columnCount{
    return _columnCount;
}

- (NSArray *)allColumnName{
    return [_columnNameToIndexMap allKeys];
}

- (NSInteger)columnIndexForName:(NSString*)columnName{
    return [_columnNameToIndexMap integerForKey:[columnName lowercaseString]];
}

- (NSString *)columnNameForIndex:(NSInteger)columnIndex{
    NSString *name = [NSString stringWithFormat:@"%@%ld", columnIndexToNameKey, (long)columnIndex];
    return [self.columnIndexToNameMap stringForKey:name];
}

- (NSInteger)intForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary integerForKey:[columnName lowercaseString]];
}

- (NSInteger)intForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self intForColumn:columnName];
}

- (long)longForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return (long)[valueDictionary longLongForKey:[columnName lowercaseString]];
}

- (long)longForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self longForColumn:columnName];
}

- (long long)longLongIntForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary longLongForKey:[columnName lowercaseString]];
}

- (long long)longLongIntForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self longLongIntForColumn:columnName];
}

- (BOOL)boolForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary boolForKey:[columnName lowercaseString]];
}

- (BOOL)boolForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self boolForColumn:columnName];
}

- (double)doubleForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary doubleForKey:[columnName lowercaseString]];
}

- (double)doubleForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self doubleForColumn:columnName];
}

- (NSString *)stringForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary stringForKey:[columnName lowercaseString]];
}

- (NSString *)stringForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self stringForColumn:columnName];
}

- (NSDate *)dateForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary dateForKey:[columnName lowercaseString]];
}

- (NSDate *)dateForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self dateForColumn:columnName];
}

- (NSData *)dataForColumn:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary dataForKey:[columnName lowercaseString]];
}

- (NSData *)dataForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self dataForColumn:columnName];
}

- (id)objectForColumnName:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary objectValueForKey:[columnName lowercaseString]];
}

- (id)objectForColumnIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self objectForColumnName:columnName];
}

- (BOOL)columnIsNullForIndex:(NSInteger)columnIndex{
    NSString *columnName = [self columnNameForIndex:columnIndex];
    return [self columnIsNullColumnName:columnName];
}

- (BOOL)columnIsNullColumnName:(NSString *)columnName{
    NSDictionary *valueDictionary = self.dataArray[self.currentRow];
    return [valueDictionary isNullForKey:[columnName lowercaseString]];
}

- (NSString *)description{
    return [NSString stringWithFormat:@"\n{\n\t\"rowCount\" : \"%lu\", \n\t\"columnCount\" : \"%lu\", \n\t\"data\" : \"%@\"\n}", (unsigned long)[self rowCount], (unsigned long)[self columnCount], [self arrayToString:self.dataArray]];
}

- (NSString *)arrayToString:(NSArray *)array{
    NSMutableString *mutableString = [NSMutableString string];
    [mutableString appendString:@"["];
    for(NSInteger index = 0; index < array.count; index ++){
        NSDictionary *dictionary = array[index];
        if(index != 0){
            [mutableString appendFormat:@"\t%@", [self dictionaryToString:dictionary]];
        }else{
            [mutableString appendString:[self dictionaryToString:dictionary]];
        }
        
        if(index != array.count - 1){
            [mutableString appendString:@", \n"];
        }
    }
    
    [mutableString appendString:@"]"];
    return [mutableString copy];
}

- (NSString *)dictionaryToString:(NSDictionary *)dictionary{
    NSMutableString *mutableString = [NSMutableString string];
    NSArray *keys = [dictionary allKeys];
    [mutableString appendString:@"{\n"];
    for(NSInteger index = 0; index < keys.count; index ++){
        NSString *key = keys[index];
        if([dictionary[key] isKindOfClass:[NSData class]]){
            // 字典中的二进制数据显示为'<blob>'
            [mutableString appendFormat:@"\t\t\"%@\" : <blob>", key];
        }else{
            [mutableString appendFormat:@"\t\t\"%@\" : \"%@\"", key, [dictionary stringForKey:key]];
        }
        
        if(index != keys.count - 1){
            [mutableString appendString:@", \n"];
        }
    }
    
    [mutableString appendString:@"\n\t}"];
    return [mutableString copy];
}

@end
